﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace FluentETL
{
    internal static class DbUtil
    {
        public static string EscapeString(string s)
        {
            return s.Replace("'", "''");
        }

        public static string SqlServerEscapeTableOrColumnName(string s)
        {
            return "[" + s + "]";
        }

        public static IList<string> GetSqlServerPrimaryKeys(IDbConnection conn, string tableName)
        {
            string sql = @"select kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME, tc.CONSTRAINT_TYPE, kcu.COLUMN_NAME, kcu.ORDINAL_POSITION
                                        from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
                                        join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
                                        on kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
                                        and kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
                                        and kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
                                        and kcu.TABLE_NAME = tc.TABLE_NAME
                                        and kcu.TABLE_NAME = '" + DbUtil.EscapeString(tableName) + @"' 
                                        where tc.CONSTRAINT_TYPE in ( 'PRIMARY KEY' )";
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            IList<string> pkColumns = new List<string>();
            using (IDataReader rdr = cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    pkColumns.Add((string)rdr["COLUMN_NAME"]);
                }
            }
            return pkColumns;
        }


        public static IList<string> GetOleDbPrimaryKeys(OleDbConnection conn, string tableName)
        {
            DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, new Object[] { null, null, tableName });

            IList<string> pkColumns = (from r in dt.AsEnumerable()
                                       select r["COLUMN_NAME"].ToString()).ToList();
            return pkColumns;
        }
    }
}
